package database;
import java.sql.ResultSet;
import java.sql.SQLException;

import main.Terminal;
import model.Customer;
import model.Sale;
import model.ShowTime;
import model.Sale.STATUS;

/**
 * 
 * @author Patrick
 *
 */

public class DBSale {
	
	private DBConnection con;
	private DBEmployee emp;
	
	//Conversion constants for Sale.STATUS enums
	private final String STATUS_FREE = "FREE";
	private final String STATUS_SOLD = "SOLD";
	private final String ID = "ID";
	private final String STATUS_RESERVED = "RESERVED";
	
	public DBSale()
	{
		con = DBConnection.getInstance();
		emp = new DBEmployee();
	}
	
	public void addSale(Sale sale, Customer cust)
	{
		int saleID = sale.getId();
		int saleDay = sale.getDay();
		int saleMonth = sale.getMonth();
		int saleYear = sale.getYear();
		
		String strStatus = "-1";	//<-- error-code
		
		if(sale.getStatus() == STATUS.FREE) {
			strStatus = STATUS_FREE;
		}
		else if(sale.getStatus() == STATUS.SOLD) {
			strStatus = STATUS_SOLD;
		}
		else /*if(sale.getStatus() == STATUS.RESERVED)		<-- not necessary */ {
			strStatus = STATUS_RESERVED;
		}
		
		if(cust != null) {
			DBCustomer dbc = new DBCustomer();
			
			
			//model.Sale.STATUS saleStatus = sale.getStatus();
			String saleEmpPhone = sale.getEmployee().getPhone();	
			String sQuery = String.format("Insert INTO Sale values ('%s' , '%s' , '%s' ," +
					"'%s' ,'%s' ,'%s')", saleID, saleDay, saleMonth, saleYear, 
					strStatus, saleEmpPhone);
			
			dbc.addCustomer(cust, sale);
			
			con.execute(sQuery);
		}
		else {
	
			
		String saleEmpPhone = sale.getEmployee().getPhone();	
		String query = String.format("Insert INTO Sale values ('%s' , '%s' , '%s' ," +
				"'%s' ,'%s' ,'%s')", saleID, saleDay, saleMonth, saleYear, 
				strStatus, saleEmpPhone);
		con.execute(query);
		}
	}
	
	public Sale getSale(String ID, String phone)
	{
		Sale result = null;
		String condition;
		if(phone != null) {
			condition = ("SELECT id, day, month, year, status, empPhone, name, phone, saleid FROM Sale INNER JOIN Customer ON Sale.ID = Customer.SaleID WHERE Customer.Phone = " + phone);
		}
		else {
			condition = ("Select id, day, month, year, status, empPhone from " +
					"Sale WHERE id = " + ID);
		}
		ResultSet resSet = con.executeResSet(condition);
		try{
			resSet.next();
			int id = resSet.getInt("ID");
			int day = resSet.getInt("day");
			int month = resSet.getInt("month");
			int year = resSet.getInt("year");
			
			STATUS saleStatus;
			
			
			//NB! These are "converted" from enum to String in the createSale (through class-constants)..
			String strStatus = resSet.getString("Status");
			if(strStatus.equals(STATUS_FREE)) {
				saleStatus = STATUS.FREE;
			}
			else if(strStatus.equals(STATUS_SOLD)) {
				saleStatus = STATUS.SOLD;
			}
			else /*final "if" not necessary */ {
				saleStatus = STATUS.RESERVED;
			}
			
			String empPhone = resSet.getString("empPhone");
			result = new Sale(id, day, month, year, saleStatus, emp.getEmployee(empPhone));
		}
		catch(Exception e) {
			Terminal.say("Could not get sale", e);
		}
		return result;
	}
	
	public int getMaxID()
	{
		int id = -1; //"error" code	TODO: change error code?
		try {
			String query = String.format("SELECT MAX(%s) AS ID FROM Sale", ID);
			ResultSet resSet = con.executeResSet(query);
			resSet.next();
			id = resSet.getInt("ID");
		}
		catch (Exception e) {
			Terminal.say("Error finding max id", e);
		}
		return id;
		
	}
	
	public void deleteReservationByPhone(String phoneNum)
	{
		//String query1 = String.format("Select FROM Sale Where CusID = '" + cus.getCustomer(phoneNum).g + "'");
		String querySelect = String.format("Select FROM Sale Where CusID = '" + phoneNum + "'");
		ResultSet resSet = con.executeResSet(querySelect);
		
		try {
			resSet.next();
		} catch (SQLException e) {
			e.printStackTrace();
		}
		
		try {
			if(resSet.getString("Status") == "Reserved")
			{
				String queryDelete = String.format("DELETE FROM Sale Where ID = '" + phoneNum + "'");
				con.execute(queryDelete);
				Terminal.say("deleted");
			}
			else
			{
				Terminal.say("Not a reservation");
			}
		} catch (SQLException e) {
			e.printStackTrace();
		}	
	}
}
